package com.zy.community.community.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zy.community.community.domain.ZyOwnerRoom;
import com.zy.community.community.domain.dto.ZyOwnerDto;
import com.zy.community.community.domain.dto.ZyOwnerRoomDto;
import com.zy.community.web.controller.mini.index.dto.CardDto;
import com.zy.community.web.controller.mini.index.dto.FamilyDto;
import com.zy.community.web.controller.mini.index.dto.MemberDto;
import com.zy.community.web.controller.mini.life.dto.BindCommonDto;
import com.zy.community.web.controller.mini.my.handle.dto.BindInfoDto;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 房屋绑定 Mapper接口
 *
 * @author yangdi
 * @date 2020-12-15
 */
public interface ZyOwnerRoomMapper extends BaseMapper<ZyOwnerRoom> {




    /**
     * 查询列表
     * @param zyOwnerRoom
     * @return
     */
    @Select("<script>" +
            "SELECT " +
            "a.owner_room_id,a.community_id,a.building_id," +
            "a.unit_id,a.room_id,a.owner_id," +
            "a.owner_type,a.room_status,a.create_by," +
            "a.create_time,a.update_by,a.update_time," +
            "a.remark,b.community_name as communityName,c.building_name as buildingName," +
            "d.unit_name as unitName,e.room_name as roomName,f.owner_real_name as ownerRealName " +
            "FROM zy_owner_room a " +
            "LEFT JOIN zy_community b on a.community_id = b.community_id " +
            "LEFT JOIN zy_building c on a.building_id = c.building_id " +
            "LEFT JOIN zy_unit d on a.unit_id = d.unit_id " +
            "LEFT JOIN zy_room e on a.room_id = e.room_id " +
            "LEFT JOIN zy_owner f on a.owner_id = f.owner_id " +
            "<where>" +
            "<if test=\"roomStatus !=null\">" +
            "and a.room_status = #{roomStatus} " +
            "</if>" +
            "and a.owner_type = #{ownerType}" +
            "\tand a.community_id = #{communityId}\n" +
            "</where>" +
            "</script>")
    List<ZyOwnerRoomDto> selectZyOwnerRoomList(ZyOwnerRoom zyOwnerRoom);

    @Select("SELECT\n" +
            "\ta.owner_room_id as ownerRoomId,\n" +
            "\tc.building_name as buildingName,\n" +
            "\td.unit_name as unitName,\n" +
            "\te.room_name as roomName,\n" +
            "\ta.owner_type as ownerType,\n" +
            "\ta.create_time as applyDate,\n" +
            "\ta.update_time as changeDate,\n" +
            "\t a.room_status as roomStatus \n" +
            "FROM\n" +
            "\tzy_owner_room a\n" +
            "\tLEFT JOIN zy_building c on a.building_id = c.building_id\n" +
            "\tLEFT JOIN zy_unit d on a.unit_id = d.unit_id\n" +
            "\tLEFT JOIN zy_room e on a.room_id = e.room_id\n" +
            " where a.community_id = #{communityId} and a.owner_id = #{ownerId} " +
            " order by a.create_time desc")
    List<BindCommonDto> findBindResult(@Param("ownerId") Long ownerId, @Param("communityId") Long communityId);


    @Select("SELECT\n" +
            "\ta.owner_room_id as ownerRoomId,\n" +
            "\ta.owner_type as ownerType,\n" +
            "\tb.owner_real_name as ownerRealName,\n" +
            "\tc.building_name as buildingName,\n" +
            "\td.unit_name as unitName,\n" +
            "\te.room_name as roomName\n" +
            "FROM\n" +
            "\tzy_owner_room a\n" +
            "\tLEFT JOIN zy_owner b ON a.owner_id = b.owner_id\n" +
            "\tLEFT JOIN zy_building c ON a.building_id = c.building_id\n" +
            "\tLEFT JOIN zy_unit d ON a.unit_id = d.unit_id\n" +
            "\tLEFT JOIN zy_room e ON a.room_id = e.room_id \n" +
            " WHERE\n" +
            " a.room_status = 'Binding' \n" +
            " AND a.community_id = #{communityId}\n" +
            " AND a.owner_id = #{ownerId}")
    List<CardDto> findOwnerCardInfo(@Param("communityId") Long communityId, @Param("ownerId") Long ownerId);

    @Select("SELECT DISTINCT\n" +
            "\troom_id \n" +
            "FROM\n" +
            "\tzy_owner_room a \n" +
            "WHERE\n" +
            "\ta.room_status = 'Binding'\n" +
            "\tand a.community_id = #{communityId} \n" +
            "\tand a.owner_id=#{ownerId}")
    List<Long> findBindRoomIds(@Param("communityId") Long communityId, @Param("ownerId") Long ownerId);


    @Select("SELECT\n" +
            "\ta.room_id as roomId,\n" +
            "\tb.building_name as buildingName,\n" +
            "\tc.unit_name as unitName,\n" +
            "\td.room_name as roomName\n" +
            "FROM\n" +
            "\tzy_owner_room a\n" +
            "\tLEFT JOIN zy_building b ON a.building_id = b.building_id\n" +
            "\tLEFT JOIN zy_unit c on a.unit_id = c.unit_id\n" +
            "\tLEFT JOIN zy_room d on a.room_id = d.room_id\n" +
            "\twhere a.room_id = #{roomId} and a.owner_id = #{ownerId}")
    FamilyDto findBasicFamily(@Param("roomId")Long roomId,@Param("ownerId")Long ownerId);

    @Select("SELECT\n" +
            "\ta.owner_type as ownerType,\n" +
            "\t\ta.owner_id as ownerId,\n" +
            "\t\tb.owner_real_name as realName,\n" +
            "\t\tb.owner_phone_number as phoneNum\n" +
            "FROM\n" +
            "\tzy_owner_room a\n" +
            "\tLEFT JOIN zy_owner b ON a.owner_id = b.owner_id \n" +
            "WHERE\n" +
            "\ta.room_status = 'Binding' \n" +
            "\tAND a.room_id = #{roomId}")
    List<MemberDto> findMembers(@Param("roomId") Long roomId);


    @Select("<script>" +
            " SELECT\n" +
            "\ta.owner_room_id as ownerRoomId,\n" +
            "\tb.building_name as buildingName,\n" +
            "\tc.unit_name as unitName,\n" +
            "\td.room_name as roomName,\n" +
            "\te.owner_real_name as ownerRealName,\n" +
            "\ta.create_time applyDate,\n" +
            "\te.owner_phone_number as phoneNum,\n" +
            "\ta.owner_type as ownerType\n" +
            " FROM\n" +
            "\tzy_owner_room a\n" +
            "\tLEFT JOIN zy_building b ON a.building_id = b.building_id\n" +
            "\tLEFT JOIN zy_unit c on a.unit_id = c.unit_id\n" +
            "\tLEFT JOIN zy_room d on a.room_id = d.room_id\n" +
            "\tLEFT JOIN zy_owner e on a.owner_id = e.owner_id\n" +
            " where a.community_id = #{communityId}\n" +
            " and a.room_status = 'Auditing'\n" +
            " and a.owner_type != 'yz'\n" +
            " and a.room_id in <foreach item='roomId' index='index' collection='roomIds' open='(' separator=',' close=')'>\n" +
            "  #{roomId} "+
            " </foreach> "+
            "\t</script>")
    List<BindInfoDto> findWaitHandleInfo(@Param("communityId")Long communityId,@Param("roomIds") List<Long> roomIds);

    /**
     * 查询list
     * @param communityId
     * @return
     */
    @Select("<script>" +
            "SELECT\n" +
            "\ta.owner_room_id,a.community_id,a.building_id,\n" +
            "\ta.unit_id,a.room_id,a.owner_id,\n" +
            "\ta.owner_type,a.room_status,a.create_by,\n" +
            "\ta.create_time,a.update_by,a.update_time,\n" +
            "\ta.remark,b.community_name as communityName,c.building_name as buildingName,\n" +
            "\td.unit_name as unitName,e.room_name as roomName,f.owner_real_name as ownerRealName,\n" +
            "\tf.owner_nickname,f.owner_real_name,f.owner_gender,\n" +
            "\tf.owner_age,f.owner_id_card,f.owner_phone_number,\n" +
            "\tf.owner_wechat_id,f.owner_qq_number,f.owner_birthday,\n" +
            "\tf.owner_portrait,f.owner_signature,f.owner_status,\n" +
            "\tf.owner_logon_mode,f.owner_type\n" +
            "\tFROM zy_owner_room a\n" +
            "\tLEFT JOIN zy_community b on a.community_id = b.community_id\n" +
            "\tLEFT JOIN zy_building c on a.building_id = c.building_id\n" +
            "\tLEFT JOIN zy_unit d on a.unit_id = d.unit_id\n" +
            "\tLEFT JOIN zy_room e on a.room_id = e.room_id\n" +
            "\tLEFT JOIN zy_owner f on a.owner_id = f.owner_id\n" +
            "\t<where>\n" +
            "\tand a.room_status = 'Binding'\n" +
            "\tand a.community_id = #{communityId}\n" +
            "\t</where>\n" +
            "</script>")
    List<ZyOwnerDto> queryZyOwnerList(Long communityId);
}
